﻿/********************************************************************************************************************
* Author:	Christos Polydorou																						*
* Email:	std04237@di.uoa.gr																						*
* Purpose:	This script is used in order to create the stored procedure "usp_getcategorieswithaccess"				*
*			that returns the category ids of all the categories that a user can access ( is owner, moderator, the	*
*			the category is public or the user is invited ) using the username of the user.							*
********************************************************************************************************************/

CREATE PROCEDURE [dbo].[usp_getcategorieswithaccess]
	@usrid bigint
AS
	DECLARE @usrtype nvarchar(3)

	SELECT @usrtype = usertype
	FROM Users
	WHERE Users.id = @usrid

	IF @usrtype = 'adm'												/* if the user is administrator return all categories */
		BEGIN
		SET NOCOUNT OFF
		SELECT [Categories].id
		FROM Categories
		WHERE categories.isdeleted != '1'
		SET NOCOUNT ON
		RETURN 0
		END
	ELSE
		BEGIN														/* select the categories that the user is the owner */
		SET NOCOUNT OFF
		SELECT [Categories].id										/* or the category is public */
		FROM [Categories]
		WHERE	[Categories].IsDeleted = 0 and
				  [Categories].IsApproved != 0 and
				  [Categories].isDeleted != 1 and (
				  [Categories].IsPrivate = '0' OR
				  [Categories].Owner = @usrid OR
				  [Categories].id in (									/* select the categories the user is invited to */
						SELECT categoryid
						FROM [category-user]
						WHERE [category-user].userid = @usrid
					) OR
				[Categories].id in (
						SELECT [Category-Moderator].Category			/* select the categories that the user is moderator */
						FROM [Category-Moderator]
						WHERE [Category-Moderator].Moderator = @usrid
					)
				)
		END
RETURN 0